数据增、删、改、查

增

INSERT INTO `student`(`name`, birthday, sex, phone)
VALUES('bozai', '1998-07-22', 0, '18235557369');

删

DELETE FROM student WHERE `name` = 'bozai';

改

UPDATE `student` SET `phone` = '15581766609' WHERE id = 1;

单表查询

SELECT id, `name` FROM `account`;  -- 查询 account 表中的指定列

SELECT `name` AS people FROM `account`;  -- 更名

SELECT * FROM `account`;  -- 查询所有列

SELECT 
CASE sex
WHEN 0 THEN '男'  -- 表头重命名
ELSE '女'
END AS gender,
id, `name` FROM account;

SELECT * FROM account WHERE sex = 0;  -- 查询符合条件的

SELECT * FROM account WHERE sex IN (0, 1);  -- 条件符合 0, 1 的所有数据

SELECT * FROM account WHERE `address` IS NULL;  -- 查询该项为空的数据
SELECT * FROM account WHERE `address` IS NOT NULL;  -- 查询该项不为空的

SELECT * FROM account WHERE sex BETWEEN 0 AND 1;  -- 小于等于 0，大于等于 1 的数据

SELECT * FROM account WHERE `name` LIKE '%y%';  -- 包含 y 字符的数据
SELECT * FROM account WHERE `name` LIKE 'y_';  -- 包含 y 字符且只有两个字符的数据

SELECT * FROM account WHERE `name` = 'yao' and pwd = '123456';  -- AND 与
SELECT * FROM account WHERE logonTime >= '2020-07-22' OR sex = 1;  -- OR 或

SELECT * FROM account ORDER BY logonTime ASC;  -- ASC 升序排列
SELECT * FROM account ORDER BY logonTime DESC;  -- DESC 降序排列
SELECT * FROM account ORDER BY sex ASC, logonTime DESC;  -- 按 sex 升序排列，logonTime 降序排列

SELECT * FROM account LIMIT 2, 3;  -- 跳过 2 条数据，取接下来的 3 条数据
-- 后端分页： limit (page-1)*pageSize, pageSize

SELECT DISTINCT sex FROM account;  -- 查看类型种类

连表查询

SELECT * FROM account, discuss;  -- 笛卡尔积

SELECT * FROM account AS t1 LEFT JOIN discuss AS t2 ON t1.id = t2.disId;  -- 左连接

SELECT * FROM account AS t1 INNER JOIN discuss AS t2 ON t1.id = t2.disId;  -- 内连接